{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "安装\n",
    "> pip install sqlalchemy\n",
    "\n",
    "https://www.cnblogs.com/iexperience/p/9511462.html"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 连接数据库"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "以操作Mysql为例：\n",
    "- 安装mysql-connector-python驱动\n",
    "  >conda install mysql-connector-python\n",
    "- Engine以及数据库Url 配置参考：https://docs.sqlalchemy.org/en/13/core/engines.html\n",
    "\n",
    "注：pymysql 驱动设置编码字符集一定要在数据库访问的URL上增加?charset=utf8，否则数据库的连接就不是utf8的编码格式"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlalchemy\n",
    "from sqlalchemy import create_engine\n",
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "from sqlalchemy import Column, Integer, String, Float, DateTime, ForeignKey\n",
    "from sqlalchemy.orm import sessionmaker, relationship"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# 初始化数据库连接:：PyMySQL\n",
    "\n",
    "engine = create_engine(\"mysql+mysqlconnector://root:123456@localhost/QA\",\n",
    "                       encoding='utf-8',\n",
    "                       echo=True)  # 打印详细日志\n",
    "\n",
    "\n",
    "# 创建与数据库的会话，class,不是实例\n",
    "DBSession = sessionmaker(bind=engine)\n",
    "\n",
    "# 生成session实例\n",
    "session = DBSession()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 初始化数据库连接:：sqlite\n",
    "\n",
    "engine = create_engine(r'sqlite:///C:\\Users\\13438\\Desktop\\git_clone\\test_ws\\db.sqlite3',\n",
    "                       encoding='utf-8',\n",
    "                       echo=True)  # 打印详细日志\n",
    "\n",
    "\n",
    "# 创建与数据库的会话，class,不是实例\n",
    "DBSession = sessionmaker(bind=engine)\n",
    "\n",
    "# 生成session实例\n",
    "session = DBSession()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`create_engine()`用来初始化数据库连接。SQLAlchemy用一个字符串表示连接信息：\n",
    "\n",
    ">'数据库类型+数据库驱动名称://用户名:口令@机器地址:端口号/数据库名'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##  创建表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 生成orm基类\n",
    "Base = declarative_base()\n",
    "\n",
    "# 定义表结构\n",
    "class User(Base):\n",
    "    __tablename__ = 'users'  #表名\n",
    "\n",
    "    id = Column(Integer, primary_key=True, doc='id')\n",
    "    name = Column(String(32), doc='姓名', nullable=False)\n",
    "    age = Column(Integer, doc='年龄', nullable=True)\n",
    "    password = Column(String(64), doc='密码', default='123')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "以上代码完成SQLAlchemy的初始化和具体每个表的class定义。如果有多个表，就继续定义其他class，例如School：\n",
    "\n",
    "```py\n",
    "class School(Base):\n",
    "    __tablename__ = 'school'\n",
    "    id = ...\n",
    "    name = ...\n",
    "```    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2020-05-13 09:58:14,235 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1\n",
      "2020-05-13 09:58:14,236 INFO sqlalchemy.engine.base.Engine ()\n",
      "2020-05-13 09:58:14,237 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1\n",
      "2020-05-13 09:58:14,239 INFO sqlalchemy.engine.base.Engine ()\n",
      "2020-05-13 09:58:14,240 INFO sqlalchemy.engine.base.Engine PRAGMA main.table_info(\"monitor_msg\")\n",
      "2020-05-13 09:58:14,241 INFO sqlalchemy.engine.base.Engine ()\n"
     ]
    }
   ],
   "source": [
    "Base.metadata.create_all(engine, checkfirst=True)  # 创建表结构"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 增"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "user_obj = User(name=\"aa\", age=18,\n",
    "                password=\"123456\")  #插入你要创建的数据对象，每执行一次都会新增一次数据。\n",
    "print(user_obj.name, user_obj.id)  #此时还没创建对象呢，不信你打印一下id发现还是None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "session.add(user_obj)  #把要创建的数据对象添加到这个session里\n",
    "\n",
    "print(user_obj.name, user_obj.id)  # 此时也依然还没创建"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "session.commit()  # 提交，使前面修改的数据生效。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "可见，关键是获取session，然后把对象添加到session，最后提交并关闭。DBSession对象可视为当前数据库连接。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 查"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# 创建Query查询，filter是where条件，最后调用one()返回唯一行，如果调用all()则返回所有行:\n",
    "user = session.query(User).filter(User.id==1).one()\n",
    "print(user.name, user.age)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "users = session.query(User).filter(User.id<5).all()\n",
    "users"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "my_user = session.query(User).filter_by(name=\"aa\").first()  #创建查询对象\n",
    "print((my_user.id, my_user.name, my_user.password))  #输出查询内容"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 改"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "my_user = session.query(User).filter_by(name=\"uu\").first()  #根据指定条件创建符合条件的对象\n",
    "print(my_user)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "my_user.name = 'zz'  #将name='yy'的name修改为uu\n",
    "print(my_user.id, my_user.name, my_user.password)  # 此时还没有提交到数据库中"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "session.commit()   # 提交修改"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 回滚"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "user_obj = User(name=\"kk\", password=\"99999\", age=10)  #插入你要创建的数据对象\n",
    "session.add(user_obj)  #把要创建的数据对象添加到这个session里\n",
    "my_user = session.query(User).filter_by(\n",
    "    name=\"rr\").first()  #根据指定条件创建符合条件的对象，first()是指name='rr'的第一条记录\n",
    "my_user.name = \"gg\"  #将name='yy'的name修改为gg\n",
    "print(session.query(User).filter(User.name.in_([\"gg\", \"kk\"])).all())  #显示修改后的数据\n",
    "\n",
    "session.rollback()  #回滚\n",
    "print(session.query(User).filter(User.name.in_([\"gg\", \"kk\"])).all())  #显示回滚后的内容"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "参考：[廖雪峰python教程](https://www.liaoxuefeng.com/wiki/1016959663602400/1017803857459008)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "165px"
   },
   "toc_section_display": true,
   "toc_window_display": true
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
